package com.slipper.system.backup.service;

import com.github.pagehelper.PageInfo;
import com.google.gson.reflect.TypeToken;
import com.slipper.common.service.CommonService;
import com.slipper.constants.Constants;
import com.slipper.core.database.IResolver;
import com.slipper.core.database.ResolverFactory;
import com.slipper.core.database.vo.*;
import com.slipper.core.file.FileServiceUtil;
import com.slipper.core.gson.GsonUtil;
import com.slipper.system.affix.bo.AffixItemBO;
import com.slipper.system.affix.service.AffixItemService;
import com.slipper.system.backup.bo.BackupBO;
import com.slipper.system.codeless.bo.*;
import com.slipper.system.codeless.service.*;
import com.slipper.system.codeless.vo.*;
import com.slipper.system.dictionary.bo.DictionaryBO;
import com.slipper.system.dictionary.bo.DictionaryDataBO;
import com.slipper.system.dictionary.service.DictionaryDataService;
import com.slipper.system.dictionary.service.DictionaryService;
import com.slipper.system.menu.bo.MenuBO;
import com.slipper.system.menu.service.MenuService;
import com.slipper.system.subsystem.bo.SubsystemBO;
import com.slipper.system.subsystem.service.SubsystemService;
import com.slipper.util.*;
import jakarta.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.io.*;
import java.lang.reflect.Type;
import java.util.*;
import java.util.stream.Collectors;

@Component
public class BackupRestoreService {
    private static final Logger logger = LoggerFactory.getLogger(BackupRestoreService.class);

    @Resource
    protected CommonService commonService;
    @Resource
    private DataModelService dataModelService;
    @Resource
    private DataFolderService dataFolderService;
    @Resource
    private DataFieldService dataFieldService;
    @Resource
    private DataFieldRelevanceService dataFieldRelevanceService;
    @Resource
    private DataTableService dataTableService;
    @Resource
    private DataTableFieldService dataTableFieldService;
    @Resource
    private DataTableFieldStyleService dataTableFieldStyleService;
    @Resource
    private DataTableOrdinalService dataTableOrdinalService;
    @Resource
    private PrivilegeService privilegeService;
    @Resource
    private PrivilegeConditionService privilegeConditionService;
    @Resource
    private DataFormService dataFormService;
    @Resource
    private DataFormModelService dataFormModelService;
    @Resource
    private DataFormFieldService dataFormFieldService;
    @Resource
    private DataFormAutofillService dataFormAutofillService;
    @Resource
    private DataFormAutofillFieldService dataFormAutofillFieldService;
    @Resource
    private DataFormDisplayService dataFormDisplayService;
    @Resource
    private DataFormDisplayRuleService dataFormDisplayRuleService;
    @Resource
    private ScriptFolderService scriptFolderService;
    @Resource
    private ScriptGroovyService scriptGroovyService;
    @Resource
    private ViewFolderService viewFolderService;
    @Resource
    private ViewService viewService;
    @Resource
    private PageService pageService;
    @Resource
    private PageFolderService pageFolderService;
    @Resource
    private PageParamService pageParamService;
    @Resource
    private PageCompService pageCompService;
    @Resource
    private PageCompParamService pageCompParamService;
    @Resource
    private PageCompFilterService pageCompFilterService;
    @Resource
    private PageCompButtonService pageCompButtonService;
    @Resource
    private PageCompButtonConditionService pageCompButtonConditionService;
    @Resource
    private PageCompButtonUpdateService pageCompButtonUpdateService;
    @Resource
    private DictionaryService dictionaryService;
    @Resource
    private DictionaryDataService dictionaryDataService;
    @Resource
    private SubsystemService subsystemService;
    @Resource
    private MenuService menuService;
    @Resource
    private AffixItemService affixItemService;
    @Resource
    private MobileService mobileService;
    @Resource
    private MobilePageService mobilePageService;

    public void backup(BackupBO backup, File file) throws Exception {
        FileWriter fileWriter = new FileWriter(file);
        BufferedWriter writer = new BufferedWriter(fileWriter);
        backupBackup(backup, writer);

        if ("自定义".equals(backup.getType())) {
            backupCustom(backup, writer);
        } else {
            backupStructure(writer);
            if (Arrays.asList(backup.getType().split(",")).contains("系统配置")) {
                backupSystemData(writer);
            }
            if (Arrays.asList(backup.getType().split(",")).contains("子系统配置")) {
                backupSubsystemData(writer);
            }
            if (Arrays.asList(backup.getType().split(",")).contains("子系统数据")) {
                backupModelTableData(writer);
            }
        }

        writer.close();
        fileWriter.close();
    }

    private void backupCustom(BackupBO backup, BufferedWriter writer) throws Exception {
        DataModelVO model = backupDataModel(backup.getModel());
        writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_MODEL, GsonUtil.toJson(model)))));
        writer.newLine();

        ScriptVO script = backupScript(backup.getScript());
        writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_SCRIPT, GsonUtil.toJson(script)))));
        writer.newLine();

        ViewVO view = backupView(backup.getView());
        writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_VIEW, GsonUtil.toJson(view)))));
        writer.newLine();

        PageVO page = backupPage(backup.getPage());
        writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_PAGE, GsonUtil.toJson(page)))));
        writer.newLine();

        DictionaryVO dictionary = backupDictionary(backup.getDictionaryIds());
        writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_DICTIONARY, GsonUtil.toJson(dictionary)))));
        writer.newLine();

        if (backup.getSubsystemIds() != null && backup.getSubsystemIds().length > 0) {
            List<SubsystemBO> subsystemList = subsystemService.queryList(backup.getSubsystemIds());
            writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_SUBSYSTEM, GsonUtil.toJson(subsystemList)))));
            writer.newLine();

            MenuBO menu = new MenuBO();
            menu.setSubsystemIds(backup.getSubsystemIds());
            List<MenuBO> menuList = menuService.queryList(menu);
            writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_MENU, GsonUtil.toJson(menuList)))));
            writer.newLine();
        }

        if (backup.getSequenceIds() != null && backup.getSequenceIds().length > 0) {
            String sequenceIds = Arrays.stream(backup.getSequenceIds()).map(id -> "'" + id + "'").collect(Collectors.joining(","));
            writeTableData(writer, "sys_sequence", "id", "select * from sys_sequence where id in(" + sequenceIds + ")");
        }

        if (backup.getFlowIds() != null && backup.getFlowIds().length > 0) {
            String flowIds = Arrays.stream(backup.getFlowIds()).map(id -> "'" + id + "'").collect(Collectors.joining(","));
            writeTableData(writer, "fw_flow", "id", "select * from fw_flow where id in(" + flowIds + ")");
            writeTableData(writer, "fw_flow_line", "id", "select * from fw_flow_line where flow_id in(" + flowIds + ")");
            writeTableData(writer, "fw_flow_line_cond", "id",
                    "select flc.* from fw_flow_line_cond flc, fw_flow_line fl where flc.line_id=fl.id and fl.flow_id in(" + flowIds + ")");
            writeTableData(writer, "fw_flow_node", "id", "select * from fw_flow_node where flow_id in(" + flowIds + ")");
        }

        if (backup.getAppIds() != null && backup.getAppIds().length > 0) {
            MobileVO mobile = new MobileVO();
            MobileBO qo = new MobileBO();
            qo.setIds(backup.getAppIds());
            mobile.setMobileList(mobileService.queryList(qo));
            mobile.getMobileList().forEach(mobileBO -> {
                MobilePageBO mobilePageQO = new MobilePageBO();
                mobilePageQO.setMobileId(mobileBO.getId());
                mobile.addMobilePageList(mobilePageService.queryList(mobilePageQO));
            });
            mobile.getMobilePageList().forEach(item -> mobile.getFileList().addAll(affixItemService.queryWithFile(Visual.parseFileId(item.getJsonText()))));
            writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_MOBILE, GsonUtil.toJson(mobile)))));
            writer.newLine();
        }

        if (backup.getModelIds() != null) {
            for (String modelId : backup.getModelIds()) {
                DataModelBO dataModelBO = dataModelService.queryBy(modelId);
                if (dataModelBO == null || !Constants.DataModel.TYPE_TABLE.equals(dataModelBO.getType())) continue;

                List<DataFieldBO> dataFieldList = dataFieldService.queryListBy(modelId);
                DataFieldBO pkDataField = dataFieldList.stream()
                        .filter(item -> Constants.YES.equals(item.getPrimaryKey()))
                        .findFirst()
                        .orElse(null);
                String[] imageNames = dataFieldList.stream()
                        .filter(item -> "图片".equals(item.getFieldType()))
                        .map(DataFieldBO::getFieldName)
                        .toList().toArray(new String[0]);
                if (pkDataField != null) {
                    writeTableData(writer, dataModelBO.getCode(), pkDataField.getFieldName(), imageNames);
                }
            }
        }
    }

    public DataModelVO backupDataModel(Map<String, Object> data) {
        DataModelVO vo = new DataModelVO();
        String[] folderIds = BeanUtil.getStrings(data, "folderIds");
        String[] modelIds = BeanUtil.getStrings(data, "modelIds");
        if (folderIds != null && folderIds.length > 0) {
            DataFolderBO folderQO = new DataFolderBO();
            folderQO.setIds(folderIds);
            vo.setDataFolderList(dataFolderService.queryList(folderQO));
        }
        if (modelIds != null && modelIds.length > 0) {
            DataModelBO modelQO = new DataModelBO();
            modelQO.setIds(modelIds);
            vo.setDataModelList(dataModelService.queryList(modelQO));

            DataFieldBO fieldQO = new DataFieldBO();
            fieldQO.setModelIds(modelIds);
            vo.setDataFieldList(dataFieldService.queryList(fieldQO));

            DataFieldRelevanceBO fieldRelevanceQO = new DataFieldRelevanceBO();
            fieldRelevanceQO.setFieldIds(vo.getDataFieldList().stream()
                    .map(DataFieldBO::getId)
                    .toList()
                    .toArray(new String[0]));
            if (fieldRelevanceQO.getFieldIds() != null && fieldRelevanceQO.getFieldIds().length > 0) {
                vo.setDataFieldRelevanceList(dataFieldRelevanceService.queryList(fieldRelevanceQO));
            }

            DataTableBO tableQO = new DataTableBO();
            tableQO.setModelIds(modelIds);
            vo.setDataTableList(dataTableService.queryList(tableQO));

            String[] tableIds = vo.getDataTableList().stream()
                    .map(DataTableBO::getId)
                    .toList()
                    .toArray(new String[0]);

            if (tableIds.length > 0) {
                DataTableFieldBO tableFieldQO = new DataTableFieldBO();
                tableFieldQO.setTableIds(tableIds);
                vo.setDataTableFieldList(dataTableFieldService.queryList(tableFieldQO));

                String[] fieldIds = vo.getDataTableFieldList().stream()
                        .map(DataTableFieldBO::getId)
                        .toList()
                        .toArray(new String[0]);
                if (fieldIds.length > 0) {
                    DataTableFieldStyleBO tableFieldStyleQO = new DataTableFieldStyleBO();
                    tableFieldStyleQO.setFieldIds(fieldIds);
                    vo.setDataTableFieldStyleList(dataTableFieldStyleService.queryList(tableFieldStyleQO));
                }

                DataTableOrdinalBO tableOrdinalQO = new DataTableOrdinalBO();
                tableOrdinalQO.setTableIds(tableIds);
                vo.setDataTableOrdinalList(dataTableOrdinalService.queryList(tableOrdinalQO));

                PrivilegeBO privilegeQO = new PrivilegeBO();
                privilegeQO.setSourceIds(tableIds);
                vo.setPrivilegeList(privilegeService.queryList(privilegeQO));

                PrivilegeConditionBO tablePrivilegeConditionQO = new PrivilegeConditionBO();
                tablePrivilegeConditionQO.setPrivilegeIds(vo.getPrivilegeList().stream()
                        .map(PrivilegeBO::getId)
                        .toList()
                        .toArray(new String[0]));
                if (tablePrivilegeConditionQO.getPrivilegeIds() != null
                        && tablePrivilegeConditionQO.getPrivilegeIds().length > 0) {
                    vo.setPrivilegeConditionList(privilegeConditionService
                            .queryList(tablePrivilegeConditionQO));
                }
            }

            vo.setDataFormList(dataFormService.queryListByModelIds(modelIds));

            String[] formIds = vo.getDataFormList().stream()
                    .map(DataFormBO::getId)
                    .toList()
                    .toArray(new String[0]);
            if (formIds.length > 0) {
                DataFormModelBO formModelQO = new DataFormModelBO();
                formModelQO.setFormIds(formIds);
                vo.setDataFormModelList(dataFormModelService.queryList(formModelQO));

                DataFormFieldBO formFieldQO = new DataFormFieldBO();
                formFieldQO.setModelIds(vo.getDataFormModelList().stream()
                        .map(DataFormModelBO::getId)
                        .toList()
                        .toArray(new String[0]));
                if (formFieldQO.getModelIds() != null && formFieldQO.getModelIds().length > 0) {
                    vo.setDataFormFieldList(dataFormFieldService.queryList(formFieldQO));
                }

                DataFormAutofillBO formAutofillQO = new DataFormAutofillBO();
                formAutofillQO.setFormIds(formIds);
                vo.setDataFormAutofillList(dataFormAutofillService.queryList(formAutofillQO));

                DataFormAutofillFieldBO formAutofillFieldQO = new DataFormAutofillFieldBO();
                formAutofillFieldQO.setAutofillIds(vo.getDataFormAutofillList().stream()
                        .map(DataFormAutofillBO::getId)
                        .toList()
                        .toArray(new String[0]));
                if (formAutofillFieldQO.getAutofillIds() != null && formAutofillFieldQO.getAutofillIds().length > 0) {
                    vo.setDataFormAutofillFieldList(dataFormAutofillFieldService.queryList(formAutofillFieldQO));
                }

                DataFormDisplayBO formDisplayQO = new DataFormDisplayBO();
                formDisplayQO.setFormIds(formIds);
                vo.setDataFormDisplayList(dataFormDisplayService.queryList(formDisplayQO));

                DataFormDisplayRuleBO formDisplayRuleQO = new DataFormDisplayRuleBO();
                formDisplayRuleQO.setDisplayIds(vo.getDataFormDisplayList().stream()
                        .map(DataFormDisplayBO::getId)
                        .toList()
                        .toArray(new String[0]));
                if (formDisplayRuleQO.getDisplayIds() != null && formDisplayRuleQO.getDisplayIds().length > 0) {
                    vo.setDataFormDisplayRuleList(dataFormDisplayRuleService.queryList(formDisplayRuleQO));
                }
            }
        }
        return vo;
    }

    public ScriptVO backupScript(Map<String, Object> data) {
        ScriptVO vo = new ScriptVO();
        String[] folderIds = BeanUtil.getStrings(data, "folderIds");
        String[] scriptIds = BeanUtil.getStrings(data, "scriptIds");
        if (folderIds != null && folderIds.length > 0) {
            ScriptFolderBO folderQO = new ScriptFolderBO();
            folderQO.setIds(folderIds);
            vo.setFolderList(scriptFolderService.queryList(folderQO));
        }
        if (scriptIds != null && scriptIds.length > 0) {
            vo.setScriptList(scriptGroovyService.queryList(scriptIds));
        }
        return vo;
    }

    public ViewVO backupView(Map<String, Object> data) {
        ViewVO vo = new ViewVO();
        String[] folderIds = BeanUtil.getStrings(data, "folderIds");
        String[] viewIds = BeanUtil.getStrings(data, "viewIds");
        if (folderIds != null && folderIds.length > 0) {
            ViewFolderBO folderQO = new ViewFolderBO();
            folderQO.setIds(folderIds);
            vo.setFolderList(viewFolderService.queryList(folderQO));
        }
        if (viewIds != null && viewIds.length > 0) {
            vo.setViewList(viewService.queryList(viewIds));
        }
        return vo;
    }

    public PageVO backupPage(Map<String, Object> data) {
        PageVO vo = new PageVO();
        String[] folderIds = BeanUtil.getStrings(data, "folderIds");
        String[] pageIds = BeanUtil.getStrings(data, "pageIds");
        if (folderIds != null && folderIds.length > 0) {
            PageFolderBO folderQO = new PageFolderBO();
            folderQO.setIds(folderIds);
            vo.setFolderList(pageFolderService.queryList(folderQO));
        }

        if (pageIds != null && pageIds.length > 0) {
            PageBO pageQO = new PageBO();
            pageQO.setIds(pageIds);
            vo.setPageList(pageService.queryList(pageQO));

            PageParamBO pageParamQO = new PageParamBO();
            pageParamQO.setPageIds(pageIds);
            vo.setPageParamList(pageParamService.queryList(pageParamQO));
            vo.setPageCompList(pageCompService.queryListBy(pageIds));

            String[] compIds = vo.getPageCompList().stream().map(PageCompBO::getId).toList().toArray(new String[0]);
            if (compIds.length > 0) {
                PageCompParamBO pageCompParamQO = new PageCompParamBO();
                pageCompParamQO.setCompIds(compIds);
                vo.setPageCompParamList(pageCompParamService.queryList(pageCompParamQO));

                PageCompFilterBO pageCompFilterQO = new PageCompFilterBO();
                pageCompFilterQO.setCompIds(compIds);
                vo.setPageCompFilterList(pageCompFilterService.queryList(pageCompFilterQO));

                PageCompButtonBO pageCompButtonQO = new PageCompButtonBO();
                pageCompButtonQO.setCompIds(compIds);
                vo.setPageCompButtonList(pageCompButtonService.queryList(pageCompButtonQO));
            }
            String[] buttonIds = vo.getPageCompButtonList().stream().map(PageCompButtonBO::getId).toList().toArray(new String[0]);
            if (buttonIds.length > 0) {
                PageCompButtonConditionBO pageCompButtonConditionBO = new PageCompButtonConditionBO();
                pageCompButtonConditionBO.setButtonIds(buttonIds);
                vo.setPageCompButtonConditionList(pageCompButtonConditionService.queryList(pageCompButtonConditionBO));

                PageCompButtonUpdateBO pageCompButtonUpdateBO = new PageCompButtonUpdateBO();
                pageCompButtonUpdateBO.setButtonIds(buttonIds);
                vo.setPageCompButtonUpdateList(pageCompButtonUpdateService.queryList(pageCompButtonUpdateBO));
            }
        }

        vo.getPageCompList().stream()
                .filter(item -> PageCompBO.TYPE_VISUAL.equals(item.getType()))
                .forEach(item -> vo.getFileList().addAll(affixItemService.queryWithFile(Visual.parseFileId(item.getVisualJson()))));
        return vo;
    }

    public DictionaryVO backupDictionary(String[] dictionaryIds) {
        DictionaryVO vo = new DictionaryVO();
        if (dictionaryIds != null && dictionaryIds.length > 0) {
            DictionaryBO folderQO = new DictionaryBO();
            folderQO.setIds(dictionaryIds);
            vo.setFolderList(dictionaryService.queryList(folderQO));

            DictionaryDataBO dataQO = new DictionaryDataBO();
            dataQO.setDictIds(dictionaryIds);
            vo.setDataList(dictionaryDataService.queryList(dataQO));
        }
        return vo;
    }

    private void backupBackup(BackupBO backup, BufferedWriter writer) throws Exception {
        String json = GsonUtil.toJson(new DataVO(DataVO.TYPE_BACKUP_INFO, GsonUtil.toJson(backup)));
        writer.write(AESUtil.encrypt(json));
        writer.newLine();
    }

    private void backupStructure(BufferedWriter writer) throws Exception {
        List<TableVO> tableList = querySysTableInfoList();
        if (tableList == null || tableList.isEmpty()) return;

        String json = GsonUtil.toJson(new DataVO(DataVO.TYPE_TABLE, GsonUtil.toJson(tableList)));
        writer.write(AESUtil.encrypt(json));
        writer.newLine();
    }

    private void backupSystemData(BufferedWriter writer) throws Exception {
        // 备份备份表配置中的表数据
        backupConfigTableData(writer, true);
    }

    private void backupSubsystemData(BufferedWriter writer) throws Exception {
        // 备份备份表配置中的表数据
        backupConfigTableData(writer, false);

        // 备份数据模型表结构
        backupModelStructure(writer);

        // 备份附件表数据（此表中包含页面中配置的图片以及模板数据）
        backupAffixData(writer);
    }

    private void backupConfigTableData(BufferedWriter writer, boolean onlyBuiltin) throws Exception {
        String sql = "select * from sys_backup_table";
        if (onlyBuiltin) sql += " where builtin='1' ";

        List<Map<String, Object>> dataList = commonService.queryList(sql);
        for (Map<String, Object> data : dataList) {
            String tableName = null;
            String pkName = null;
            String builtinSql = null;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (entry.getKey().equalsIgnoreCase("table_name")) {
                    tableName = entry.getValue().toString();
                }
                if (entry.getKey().equalsIgnoreCase("pk_name")) {
                    pkName = (String) entry.getValue();
                }
                if (entry.getKey().equalsIgnoreCase("builtin_sql")) {
                    builtinSql = (String) entry.getValue();
                }
            }
            if (Tool.isNotBlank(tableName) && Tool.isNotBlank(pkName)) {
                writeTableData(writer, tableName, pkName, onlyBuiltin ? builtinSql : null);
            }
        }
    }

    private void backupModelStructure(BufferedWriter writer) throws Exception {
        List<TableVO> tableList = queryModelTableInfoList();
        if (tableList == null || tableList.isEmpty()) return;

        String json = GsonUtil.toJson(new DataVO(DataVO.TYPE_TABLE, GsonUtil.toJson(tableList)));
        writer.write(AESUtil.encrypt(json));
        writer.newLine();
    }

    private void backupAffixData(BufferedWriter writer) throws Exception {
        String sql = "select ai.save_path " +
                "from sys_affix_item ai " +
                "where ai.builtin='1' or ai.id in(select t.file_id from cl_template t)";
        List<Map<String, Object>> dataList = commonService.queryList(sql);
        List<Map<String, Object>> backupList = new ArrayList<>();
        for (Map<String, Object> data : dataList) {
            if (Tool.isBlank(data.get("save_path"))) return;

            try {
                String base64Data = FileServiceUtil.readFileToBase64(data.get("save_path").toString());
                if (Tool.isBlank(base64Data)) return;
                data.put("base64Data", base64Data);
                backupList.add(data);
            } catch (Exception ignored) {}
        }
        writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_AFFIX, GsonUtil.toJson(backupList)))));
        writer.newLine();
    }

    private void backupModelTableData(BufferedWriter writer) throws Exception {
        String sql = "select dm.code, df.field_name "
                + "from cl_data_model dm "
                + "    left join cl_data_field df on df.model_id=dm.id and df.primary_key='1' "
                + "where type='1' ";
        List<Map<String, Object>> dataList = commonService.queryList(sql);
        for (Map<String, Object> data : dataList) {
            String table = null;
            String pkName = null;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (entry.getKey().equalsIgnoreCase("code")
                        && entry.getValue() != null
                        && entry.getValue().toString().toLowerCase().startsWith("dm_")) {
                    table = entry.getValue().toString();
                }
                if (entry.getKey().equalsIgnoreCase("field_name")) {
                    pkName = (String) entry.getValue();
                }
            }
            if (table != null && pkName != null) {
                writeTableData(writer, table, pkName);
            }
        }
    }

    private void writeTableData(BufferedWriter writer, String table, String pkName) throws Exception {
        writeTableData(writer, table, pkName, null, null);
    }

    private void writeTableData(BufferedWriter writer, String table, String pkName, String[] imageNames) throws Exception {
        writeTableData(writer, table, pkName, imageNames, null);
    }

    private void writeTableData(BufferedWriter writer, String table, String pkName, String sql) throws Exception {
        writeTableData(writer, table, pkName, null, sql);
    }

    private void writeTableData(BufferedWriter writer, String table, String pkName, String[] imageNames, String sql) throws Exception {
        if (Tool.isBlank(sql)) sql = "select * from " + table;

        List<Map<String, Object>> dataList;
        int count = commonService.queryTotal("select count(a.*) from (" + sql + ") a");
        if (count <= 5000) {
            dataList = commonService.queryList(sql);
        } else {
            dataList = new ArrayList<>();
            int pageSize = 5000;
            int pageNumber = count / pageSize;
            Map<String, Object> paramMap = new HashMap<>();
            paramMap.put("sql", sql);
            for (int i = 0; i < pageNumber; i++) {
                PageInfo<Map<String, Object>> pageInfo = commonService.queryPage(pageNumber, pageSize, paramMap);
                dataList.addAll(pageInfo.getList());
                if (pageInfo.getList().size() < pageSize) {
                    break;
                }
            }
        }

        if (!dataList.isEmpty()) {
            writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_TABLE_MAP_DATA, table, pkName, GsonUtil.toJson(dataList)))));
            writer.newLine();

            if (imageNames != null && imageNames.length > 0) {
                List<String> fileIdList = new ArrayList<>();
                dataList.forEach(data -> {
                    for (String imageName : imageNames) {
                        Object fieldId = data.get(imageName);
                        if (fieldId != null) {
                            fileIdList.add(fieldId.toString());
                        }
                    }
                });

                if (!fileIdList.isEmpty()) {
                    List<AffixItemBO> itemList = affixItemService.queryWithFile(fileIdList.toArray(new String[0]));
                    writer.write(AESUtil.encrypt(GsonUtil.toJson(new DataVO(DataVO.TYPE_AFFIX_ITEM, GsonUtil.toJson(itemList)))));
                    writer.newLine();
                }
            }
        }
    }

    public BackupBO restore(BufferedReader reader) throws Exception {
        BackupBO backup = null;
        Type tableType = new TypeToken<List<TableVO>>(){}.getType();
        Type dataType = new TypeToken<List<Map<String, Object>>>(){}.getType();

        // 解释数据
        String line;
        List<DataVO> dataVOList = new ArrayList<>();
        while ((line = reader.readLine()) != null) {
            String json = AESUtil.decrypt(line);
            DataVO dataVO = GsonUtil.fromJson(json, DataVO.class);
            if (DataVO.TYPE_BACKUP_INFO.equals(dataVO.getType())) {
                backup = GsonUtil.fromJson(dataVO.getJson(), BackupBO.class);
            } else {
                dataVOList.add(dataVO);
            }
        }

        // 恢复表结构
        for (DataVO dataVO : dataVOList) {
            if (DataVO.TYPE_TABLE.equals(dataVO.getType())) {
                List<TableVO> tableList = GsonUtil.fromJson(dataVO.getJson(), tableType);
                restoreStructure(tableList);
            }
        }

        // 恢复表数据
        for (DataVO dataVO : dataVOList) {
            if(DataVO.TYPE_TABLE_MAP_DATA.equals(dataVO.getType())) {
                List<Map<String, Object>> dataList = GsonUtil.fromJson(dataVO.getJson(), dataType);
                restoreTableData(dataVO.getTable(), dataVO.getPkName(), dataList, backup);
            } else if (DataVO.TYPE_AFFIX.equals(dataVO.getType())) {
                List<Map<String, Object>> dataList = GsonUtil.fromJson(dataVO.getJson(), dataType);
                restoreAffixData(dataList);
            } else if (DataVO.TYPE_AFFIX_ITEM.equals(dataVO.getType())) {
                restoreAffixItemData(dataVO.getJson());
            } else if (DataVO.TYPE_MODEL.equals(dataVO.getType())) {
                restoreDataModel(dataVO.getJson());
            } else if (DataVO.TYPE_SCRIPT.equals(dataVO.getType())) {
                restoreScript(dataVO.getJson());
            } else if (DataVO.TYPE_VIEW.equals(dataVO.getType())) {
                restoreView(dataVO.getJson());
            } else if (DataVO.TYPE_PAGE.equals(dataVO.getType())) {
                restorePage(dataVO.getJson());
            } else if (DataVO.TYPE_DICTIONARY.equals(dataVO.getType())) {
                restoreDictionary(dataVO.getJson());
            } else if (DataVO.TYPE_SUBSYSTEM.equals(dataVO.getType())) {
                restoreSubsystem(dataVO.getJson());
            } else if (DataVO.TYPE_MENU.equals(dataVO.getType())) {
                restoreMenu(dataVO.getJson());
            } else if (DataVO.TYPE_MOBILE.equals(dataVO.getType())) {
                restoreMobile(dataVO.getJson());
            }
        }

        // 重建视图
        restoreView();
        return backup;
    }

    private void restoreStructure(List<TableVO> tableList) {
        List<TableVO> sysTableList = querySysTableInfoList();
        List<TableVO> modelTableList = queryModelTableInfoList();
        tableList.forEach(table -> {
            TableVO oldTable = getTable(table.getName(), sysTableList, modelTableList);
            if (oldTable == null) {
                createTable(table);
            } else {
                updateTable(table, oldTable);
            }
        });
    }

    private void restoreAffixData(List<Map<String, Object>> dataList) throws Exception {
        for (Map<String, Object> data : dataList) {
            if (Tool.isBlank(data.get("save_path"))) return;
            if (Tool.isBlank(data.get("base64Data"))) return;

            String path = data.get("save_path").toString();
            FileServiceUtil.saveFileToPath(path, Base64.getDecoder().decode(data.get("base64Data").toString()));
        }
    }

    private void restoreAffixItemData(String content) {
        Type type = new TypeToken<List<AffixItemBO>>(){}.getType();
        affixItemService.saveList(GsonUtil.fromJson(content, type));
    }

    private void restoreView() {
        String sql = "select * from cl_view";
        List<Map<String, Object>> dataList = commonService.queryList(sql);
        IResolver resolver = getResolver();
        for (Map<String, Object> data : dataList) {
            String viewName = null;
            String statement = null;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (entry.getKey().equalsIgnoreCase("code")) {
                    viewName = (String) entry.getValue();
                }
                if (entry.getKey().equalsIgnoreCase("sql_text")) {
                    statement = (String) entry.getValue();
                }
            }
            if (Tool.isNotBlank(viewName) && Tool.isNotBlank(statement)) {
                for(String ddl : resolver.createOrUpdateViewDDL(viewName, statement)) {
                    logger.info("执行建视图DDL: {}", ddl);
                    commonService.executeDDL(ddl);
                }
            }
        }
    }

    public void restoreDataModel(String content) {
        DataModelVO vo = GsonUtil.fromJson(content, DataModelVO.class);
        dataFolderService.saveList(vo.getDataFolderList());
        dataModelService.saveList(vo.getDataModelList());
        dataFieldService.saveList(vo.getDataFieldList());
        dataFieldRelevanceService.saveList(vo.getDataFieldRelevanceList());
        dataTableService.saveList(vo.getDataTableList());
        dataTableFieldService.saveList(vo.getDataTableFieldList());
        dataTableFieldStyleService.saveList(vo.getDataTableFieldStyleList());
        dataTableOrdinalService.saveList(vo.getDataTableOrdinalList());
        privilegeService.saveList(vo.getPrivilegeList());
        privilegeConditionService.saveList(vo.getPrivilegeConditionList());
        dataFormService.saveList(vo.getDataFormList());
        dataFormModelService.saveList(vo.getDataFormModelList());
        dataFormFieldService.saveList(vo.getDataFormFieldList());
        dataFormAutofillService.saveList(vo.getDataFormAutofillList());
        dataFormAutofillFieldService.saveList(vo.getDataFormAutofillFieldList());
        dataFormDisplayService.saveList(vo.getDataFormDisplayList());
        dataFormDisplayRuleService.saveList(vo.getDataFormDisplayRuleList());
        dataModelService.createTable(vo.getDataModelList());
    }

    public void restoreScript(String content) {
        ScriptVO vo = GsonUtil.fromJson(content, ScriptVO.class);
        scriptFolderService.saveList(vo.getFolderList());
        scriptGroovyService.saveList(vo.getScriptList());
    }

    public void restoreView(String content) {
        ViewVO vo = GsonUtil.fromJson(content, ViewVO.class);
        viewFolderService.saveList(vo.getFolderList());
        viewService.saveList(vo.getViewList());
    }

    public void restorePage(String content) {
        PageVO vo = GsonUtil.fromJson(content, PageVO.class);
        pageFolderService.saveList(vo.getFolderList());
        pageService.saveList(vo.getPageList());
        pageParamService.saveList(vo.getPageParamList());
        pageCompService.saveList(vo.getPageCompList());
        pageCompParamService.saveList(vo.getPageCompParamList());
        pageCompFilterService.saveList(vo.getPageCompFilterList());
        pageCompButtonService.saveList(vo.getPageCompButtonList());
        pageCompButtonConditionService.saveList(vo.getPageCompButtonConditionList());
        pageCompButtonUpdateService.saveList(vo.getPageCompButtonUpdateList());
        affixItemService.saveList(vo.getFileList());
    }

    public void restoreDictionary(String content) {
        DictionaryVO vo = GsonUtil.fromJson(content, DictionaryVO.class);
        dictionaryService.saveList(vo.getFolderList());
        dictionaryDataService.saveList(vo.getDataList());
    }

    public void restoreSubsystem(String content) {
        Type type = new TypeToken<List<SubsystemBO>>(){}.getType();
        List<SubsystemBO> subsystemList = GsonUtil.fromJson(content, type);
        subsystemService.saveList(subsystemList);
    }

    public void restoreMenu(String content) {
        Type type = new TypeToken<List<MenuBO>>(){}.getType();
        menuService.saveList(GsonUtil.fromJson(content, type));
    }

    public void restoreMobile(String content) {
        MobileVO vo = GsonUtil.fromJson(content, MobileVO.class);
        mobileService.saveList(vo.getMobileList());
        mobilePageService.saveList(vo.getMobilePageList());
        affixItemService.saveList(vo.getFileList());
    }

    private List<TableVO> querySysTableInfoList() {
        Map<String, TableVO> tableMap = new HashMap<>();
        IResolver resolver = getResolver();
        resolver.parseTable(tableMap, commonService.queryList(resolver.tableColumnListSQL("sys_%", "ai_%", "cl_%", "dc_%", "fw_%")));
        resolver.parseIndex(tableMap, commonService.queryList(resolver.tableIndexSQL("sys_%", "ai_%", "cl_%", "dc_%", "fw_%")));
        return tableMap.values().stream().toList();
    }

    private List<TableVO> queryModelTableInfoList() {
        Map<String, TableVO> tableMap = new HashMap<>();
        IResolver resolver = getResolver();
        resolver.parseTable(tableMap, commonService.queryList(resolver.tableColumnListSQL("dm_%")));
        resolver.parseIndex(tableMap, commonService.queryList(resolver.tableIndexSQL("dm_%")));
        return tableMap.values().stream().toList();
    }

    private void createTable(TableVO table) {
        for (String ddl : getResolver().createTableDDL(table)) {
            logger.info("执行建表DDL: {}", ddl);
            commonService.executeDDL(ddl);
        }
    }

    private void updateTable(TableVO newTable, TableVO oldTable) {
        newTable.getFieldList().sort(Comparator.comparingInt(FieldVO::getOrdinal));
        newTable.getFieldList().forEach(newField -> {
            FieldVO oldField = getField(newField.getName(), oldTable.getFieldList());
            if (oldField == null) {
                addField(newTable.getName(), newField);
            } else {
                updateField(newTable.getName(), newField, oldField);
            }
        });

        newTable.getIndexList().forEach(newIndex -> {
            IndexVO oldIndex = getIndex(newIndex.getName(), oldTable.getIndexList());
            if (oldIndex == null) {
                addIndex(newTable.getName(), newIndex);
            } else {
                updateIndex(newTable.getName(), newIndex, oldIndex);
            }
        });
    }

    private void addField(String tableName, FieldVO field) {
        String ddl = getResolver().addFieldDDL(tableName, field);
        logger.info("执行新增字段DDL: {}", ddl);
        commonService.executeDDL(ddl);
    }

    private void updateField(String tableName, FieldVO newField, FieldVO oldField) {
        if (isNotChange(newField, oldField)) return;

        for (String ddl : getResolver().modifyFieldDDL(tableName, newField, oldField)) {
            logger.info("执行修改字段DDL: {}", ddl);
            commonService.executeDDL(ddl);
        }
    }

    private void addIndex(String tableName, IndexVO field) {
        String ddl = getResolver().addIndexDDL(tableName, field);
        logger.info("执行新增索引DDL: {}", ddl);
        commonService.executeDDL(ddl);
    }

    private void updateIndex(String tableName, IndexVO newIndex, IndexVO oldIndex) {
        if (isNotChange(newIndex, oldIndex)) return;
        String ddl = getResolver().dropIndexDDL(tableName, oldIndex.getName());
        logger.info("执行删除索引DDL: {}", ddl);
        commonService.executeDDL(ddl);
        addIndex(tableName, newIndex);
    }

    private void restoreTableData(String tableName, String pkName, List<Map<String, Object>> dataList, BackupBO backup) {
        List<Object> idList = dataList.stream().map(data -> data.get(pkName)).toList();

        int pageSize = 1000;
        for (int pageNumber=1;; pageNumber++) {
            int start = (pageNumber - 1) * pageSize;
            int end = pageNumber * pageSize;

            List<Object> queryIdList;
            if (idList.size() >= end) {
                queryIdList = idList.subList(start, end);
            } else if (idList.size() > start) {
                queryIdList = idList.subList(start, idList.size());
            } else {
                queryIdList = null;
            }
            if (queryIdList == null || queryIdList.isEmpty()) break;

            String sql = "select * from " + wrapSymbol(tableName) +
                    " where " + wrapSymbol(pkName) +
                    "   in(" +
                            queryIdList.stream()
                                .map(id -> "'" + id.toString() + "'")
                                .collect(Collectors.joining(", ")) +
                    "    )";
            List<Map<String, Object>> persistList = commonService.queryList(sql);
            List<Map<String, Object>> insertList;
            List<Map<String, Object>> updateList;
            if (persistList.isEmpty()) {
                insertList = dataList.stream().filter(data -> queryIdList.contains(data.get(pkName))).toList();
                updateList = new ArrayList<>();
            } else if (persistList.size() == queryIdList.size()) {
                insertList = new ArrayList<>();
                updateList = dataList.stream().filter(data -> queryIdList.contains(data.get(pkName))).toList();
            } else {
                insertList = new ArrayList<>();
                updateList = new ArrayList<>();
                for (Object id : queryIdList) {
                    if (persistList.stream().anyMatch(persist -> persist.get(pkName).equals(id))) {
                        updateList.addAll(dataList.stream().filter(data -> data.get(pkName).equals(id)).toList());
                    } else {
                        insertList.addAll(dataList.stream().filter(data -> data.get(pkName).equals(id)).toList());
                    }
                }
            }
            commonService.batchInsert(tableName, insertList);

            if (updateList.isEmpty()) return;

            // 使用系统配置重建数据时，跳过以下表数据
            if (backup.getType() != null && !backup.getType().contains("子系统")
                    && (tableName.toLowerCase().contains("sys_setting")
                        || tableName.toLowerCase().contains("sys_dictionary")
                        || tableName.toLowerCase().contains("sys_dictionary_data")
                        || tableName.toLowerCase().contains("sys_employee")
                        || tableName.toLowerCase().contains("sys_icon")
                        || tableName.toLowerCase().contains("sys_menu_privilege")
                        || tableName.toLowerCase().contains("sys_restore")
                        || tableName.toLowerCase().contains("cl_page_button")
                        || tableName.toLowerCase().contains("cl_script_tool_detail"))) return;

            if (tableName.toLowerCase().contains("cl_data_index")
                    || tableName.toLowerCase().contains("sys_operator_log")
                    || tableName.toLowerCase().contains("sys_schedule_log")) return;

            // 不更新 sys_subsystem 表数据type为管理端、门户端的数据
            if (tableName.toLowerCase().contains("sys_subsystem")) {
                updateList = updateList.stream()
                        .filter(update -> !Constants.Subsystem.TYPE_MANAGE.equals(update.get("type"))
                                && !Constants.Subsystem.TYPE_PORTAL.equals(update.get("type")))
                        .toList();
            }

            commonService.batchUpdate(tableName, pkName, updateList);
        }
    }

    private boolean isNotChange(FieldVO newField, FieldVO oldField) {
        boolean change = !newField.getType().equalsIgnoreCase(oldField.getType());
        change = change || newField.isNullable() != oldField.isNullable();

        if (!change) {
            long newLength = newField.getLength() == null ? 0 : (newField.getLength() > 4000 ? -1 : newField.getLength());
            long newPrecision = newField.getPrecision() == null ? 0 : (newField.getPrecision() > 4000 ? -1 : newField.getPrecision());
            long oldLength = oldField.getLength() == null ? 0 : (oldField.getLength() > 4000 ? -1 : oldField.getLength());
            long oldPrecision = oldField.getPrecision() == null ? 0 : (oldField.getPrecision() > 4000 ? -1 : oldField.getPrecision());

            change = switch (newField.getType()) {
                case "小数" -> newLength != oldLength || newPrecision != oldPrecision;
                case "字符", "字符串" -> newLength != oldLength;
                default -> false;
            };
        }

        return !change;
    }

    private FieldVO getField(String fieldName, List<FieldVO> fieldList) {
        for (FieldVO field : fieldList) {
            if (field.getName().equalsIgnoreCase(fieldName)) {
                return field;
            }
        }

        return null;
    }

    private boolean isNotChange(IndexVO newIndex, IndexVO oldIndex) {
        boolean change = newIndex.isUnique() != oldIndex.isUnique();
        for (IndexFieldVO field : newIndex.getFieldList()) {
            if (getIndexField(field.getName(), oldIndex.getFieldList()) == null) {
                change = true;
                break;
            }
        }
        return !change;
    }

    private IndexFieldVO getIndexField(String fieldName, List<IndexFieldVO> fieldList) {
        for (IndexFieldVO field : fieldList) {
            if (field.getName().equalsIgnoreCase(fieldName)) {
                return field;
            }
        }

        return null;
    }

    private IndexVO getIndex(String indexName, List<IndexVO> indexList) {
        for (IndexVO index : indexList) {
            if (index.getName().equalsIgnoreCase(indexName)) {
                return index;
            }
        }

        return null;
    }

    private TableVO getTable(String tableName, List<TableVO> sysTableList, List<TableVO> modelTableList) {
        for (TableVO table : sysTableList) {
            if (table.getName().equalsIgnoreCase(tableName)) {
                return table;
            }
        }

        for (TableVO table : modelTableList) {
            if (table.getName().equalsIgnoreCase(tableName)) {
                return table;
            }
        }

        return null;
    }

    public IResolver getResolver() {
        return ResolverFactory.getResolver(DatabaseUtil.getType());
    }

    public String wrapSymbol(String value) {
        return DatabaseUtil.wrapSymbol(value);
    }
}
